"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 触发器
Case Name   : 只有触发器所在表的所有者可以执行ALTER TRIGGER操作
Create At   : 2020/11/12
Owner       : opentestcase003
Description :
    1.创建用户
    2.创建表
    3.创建触发器函数
    4.创建触发器
    5.修改触发器名
    6.清理环境
Expect      :
    1.创建用户成功
    2.创建表成功
    3.触发器函数创建成功
    4.触发器创建成功
    5.修改触发器名失败
    6.清理环境成功
History     :
    2022/9/21 modifi by opentestcase054 根据代码变更优化用例，修改预期并修改用例格式
"""

import os
import unittest
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Trigger(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.u_name = 'u_trigger_0028'
        self.t_name = 't_trigger_0028'
        self.t_name2 = 't_trigger_0028_1'
        self.f_name = 'f_trigger_0028()'
        self.tri_name = 'tri_trigger_0028'

    def test_trigger(self):
        step1_txt = '----step1: 创建用户，expect: 成功----'
        self.log.info(step1_txt)
        sql_cmd1 = f"drop user if exists {self.u_name};" \
            f"create user {self.u_name} password '{macro.COMMON_PASSWD}';"
        msg1 = self.pri_sh.execut_db_sql(sql_cmd1)
        self.log.info(msg1)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, msg1,
                      '执行失败:' + step1_txt)

        step2_txt = '----step2: 建表，expect: 成功----'
        self.log.info(step2_txt)
        sql_cmd2 = f"drop table if exists {self.t_name};" \
                   f"drop table if exists {self.t_name2};" \
                   f"create table {self.t_name}(id1 INT, id2 INT, id3 INT);"\
                   f"create table {self.t_name2}(id1 INT, id2 INT, id3 INT);"
        msg2 = self.pri_sh.execut_db_sql(sql_cmd2)
        self.log.info(msg2)
        self.assertTrue(msg2.count(self.constant.CREATE_TABLE_SUCCESS) == 2,
                        '执行失败:' + step2_txt)

        step3_txt = '----step3: 创建触发器函数，expect: 成功----'
        self.log.info(step3_txt)
        sql_cmd3 = f"create or replace function {self.f_name} returns " \
                   f"trigger as \$\$ declare begin " \
                   f"insert into {self.t_name2} values(new.id1, new.id2, " \
                   f"new.id3); return new; end \$\$ language plpgsql;"
        msg3 = self.pri_sh.execut_db_sql(sql_cmd3)
        self.log.info(msg3)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, msg3,
                      '执行失败:' + step3_txt)

        step4_txt = '----step4: 创建触发器，expect: 成功----'
        self.log.info(step4_txt)
        sql_cmd4 = f"drop trigger if exists {self.tri_name} on {self.t_name};"\
                   f"create trigger {self.tri_name} before insert on " \
                   f"{self.t_name} for each row execute procedure " \
                   f"{self.f_name};"
        msg4 = self.pri_sh.execut_db_sql(sql_cmd4)
        self.log.info(msg4)
        self.assertIn(self.constant.TRIGGER_CREATE_SUCCESS_MSG, msg4,
                      '执行失败:' + step4_txt)

        step5_txt = '----step5: 修改触发器名，expect: 合理报错----'
        self.log.info(step5_txt)
        sql_cmd5 = f"alter trigger {self.tri_name} on {self.t_name} " \
                   f"rename to new_{self.tri_name};"
        excute_cmd = f'-U {self.u_name} -W "{macro.COMMON_PASSWD}" '
        msg5 = self.pri_sh.execut_db_sql(sql_cmd5, excute_cmd)
        self.log.info(msg5)
        self.assertIn('ERROR:  permission denied for relation', msg5,
                      '执行失败:' + step5_txt)

    def tearDown(self):
        self.log.info('----this is teardown----')
        step6_txt = '----step6: 清理环境----'
        self.log.info(step6_txt)
        drop_sql = f"drop trigger {self.tri_name} on {self.t_name};" \
                   f"drop function {self.f_name} cascade;" \
                   f"drop table {self.t_name} cascade;" \
                   f"drop table {self.t_name2} cascade;" \
                   f"drop user {self.u_name};"
        drop_result = self.pri_sh.execut_db_sql(drop_sql)
        self.log.info(drop_result)
        self.assertTrue(
            drop_result.count(self.constant.TRIGGER_DROP_SUCCESS_MSG) == 1 and
            drop_result.count(self.constant.DROP_FUNCTION_SUCCESS_MSG) == 1 and
            drop_result.count(self.constant.DROP_TABLE_SUCCESS) == 2 and
            drop_result.count(self.constant.DROP_ROLE_SUCCESS_MSG) == 1,
            '执行失败:' + step6_txt)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
